package com.dataex.dbtool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author zhangjxd
 * @since 2014/4/15
 */
public class BaseDbcpDao {
	private final static Logger logger = LoggerFactory.getLogger(BaseDbcpDao.class);
	private static DbcpPool dbPool = null;

	public BaseDbcpDao(String zkPath) {
		// dbPool = DbcpPool.getInstance(zkPath);
	}

	/**
	 * 初始化连接池
	 * 
	 * @param strDb
	 *            ","分割多个库,
	 *            172.18.2.174:5432:gzq_analyse:gzq_analyse:jicheng_analyse,,->
	 *            ip:port:db:user:pwd
	 * @param db
	 *            1 使用字符串, 0 使用zk
	 */
	public BaseDbcpDao(String strDb, boolean zk, int dbType) {
		if (!zk)
			dbPool = DbcpPool.getInstanceByStr(strDb, dbType);
		// else
		// dbPool = DbcpPool.getInstance(strDb);
	}

	public void resetPool(String dbConn, int dbType) {
		try {
			dbPool.shutdownDataSource(0);
		} catch (SQLException e) {
			logger.info("" + e);
		}
		dbPool = DbcpPool.getInstanceByStr(dbConn, dbType);
	}

	/**
	 * 查询数据 返回一条或多条结果集
	 * 
	 * @param dbkey
	 *            按照此key作为分库的依据
	 * @param sql
	 *            prepare sql
	 * @param parameters
	 *            占位符参数
	 * @param callBack
	 *            回调函数
	 * @return Object
	 */
	@SuppressWarnings("rawtypes")
	public Object query(long dbkey, String sql, ArrayList parameters, CallBack callBack) {
		return executeQuery(dbkey, sql, parameters, callBack);
	}

	/**
	 * 刪除数据
	 * 
	 * @param dbkey
	 *            按照此key作为分库的依据
	 * @param sql
	 *            prepare sql
	 * @param parameters
	 *            占位符参数
	 * @return int
	 */
	@SuppressWarnings("rawtypes")
	public int delete(long dbkey, String sql, ArrayList parameters) {
		return executeUpdate(dbkey, sql, parameters);
	}

	/**
	 * 
	 * @param dbkey
	 * @param tableName
	 * @param dataList
	 * @param keyCol
	 * @param conn
	 * @return
	 */
	@SuppressWarnings("unchecked")
	protected int updateMapObjects(long dbkey, String tableName, List<?> dataList, Set<String> keyCol, Connection conn, boolean bcommit) {

		if ( ImpCollUtil.isNullColl(dataList) ){
			return 1;
		}
		Map<String, Object> typeVal = (Map<String, Object>) dataList.get(0);

		PreparedStatement smt = null;
		try {
			if (bcommit)
				conn.setAutoCommit(false);
			for (int i = 0; i < dataList.size(); i++) {

				String sql = SqlAdapter.getSqlUpdateStr(tableName, (Map<String, Object>) dataList.get(i), keyCol);
				smt = conn.prepareStatement(sql);

				int cur = 1;
				typeVal = (Map<String, Object>) (dataList.get(i));
				for (Map.Entry<String, Object> en : typeVal.entrySet()) {
					smt.setObject(cur++, en.getValue());
				}

				for (String strK : keyCol) {
					smt.setObject(cur++, typeVal.get(strK));
				}

				smt.execute();
			}

		} catch (SQLException e) {
			if (bcommit) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					logger.error("" + e);
				}
			}
			logger.error("" + e);
			return -1;
		} finally {
			dbPool.closeConnection(null, smt, null);
		}

		if (bcommit) {
			try {
				conn.commit();
			} catch (SQLException e) {
				logger.error("" + e);
				return -1;
			}
		}
		return 1;
	}
	
	/**
	 * 
	 * @param dbkey
	 * @param tableName
	 * @param listMapD
	 * @param keyCol
	 * @param conn
	 * @return
	 */
	public int updateObjectsMapByConn(String tableName, List<Map<String, Object>> listMapD, Set<String> keyCol, Connection conn, String prefix, boolean sameSmt) {

		if ( ImpCollUtil.isNullColl(listMapD) ){
			return 1;
		}
		Map<String, Object> typeVal = (Map<String, Object>) listMapD.get(0);

		PreparedStatement smt = null;
		int errIndex = 0;
		try {

			String sql = SqlAdapter.getSqlUpdateStr(tableName, typeVal, keyCol);
			sql = prefix + sql;
			smt = conn.prepareStatement(sql);
			
			for (int i = 0; i < listMapD.size(); i++) {

				if ( !sameSmt ){
					sql = SqlAdapter.getSqlUpdateStr(tableName, (Map<String, Object>) listMapD.get(i), keyCol);
					smt = conn.prepareStatement(sql);				
				}

				int cur = 1;
				typeVal = (Map<String, Object>) (listMapD.get(i));
				for (Map.Entry<String, Object> en : typeVal.entrySet()) {
					smt.setObject(cur++, SqlAdapter.getSqlFieldValueObject( tableName, en.getKey(), en.getValue(), null));
				}

				for (String strK : keyCol) {
					smt.setObject(cur++, typeVal.get(strK));
				}
				errIndex = i;			
				smt.execute();
			}

		} catch (SQLException e) {
			logger.error("genSql: {}\r\n, errData:{}\r\n",  SqlAdapter.getSqlInsertStrWithValue(tableName, listMapD.get(errIndex)), listMapD.get(errIndex));
			logger.error("", e);
			return -1;
		} finally {
			dbPool.closeConnection(null, smt, null);
		}
		return 1;
	}

	/**
	 * 
	 * @param dbkey
	 * @param tableName
	 * @param dataList
	 * @param keyCol
	 * @param conn
	 * @return
	 */
	@SuppressWarnings("unchecked")
	protected int deleteMapObjects(long dbkey, String tableName, List<?> dataList, Set<String> keyCol, Connection conn, boolean bcommit) {

		Map<String, Object> typeVal = (Map<String, Object>) dataList.get(0);

		PreparedStatement smt = null;
		try {
			if (bcommit)
				conn.setAutoCommit(false);
			for (int i = 0; i < dataList.size(); i++) {

				String sql = SqlAdapter.getSqlDeleteStr(tableName, (Map<String, Object>) dataList.get(i), keyCol);
				smt = conn.prepareStatement(sql);

				int cur = 1;
				for (String strK : keyCol) {
					smt.setObject(cur++, typeVal.get(strK));
				}

				smt.execute();
			}

		} catch (SQLException e) {
			if (bcommit) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					logger.error("" + e);
				}
			}
			logger.error("" + e);
			return -1;
		} finally {
			dbPool.closeConnection(null, smt, null);
		}

		if (bcommit) {
			try {
				conn.commit();
			} catch (SQLException e) {
				logger.error("" + e);
				return -1;
			}
		}
		return 1;
	}

	/**
	 * 
	 * @param dbkey
	 * @param tableName
	 * @param dataList
	 * @param keyCol
	 * @return
	 */
	public int updateObjects(long dbkey, String tableName, List<?> dataList, Set<String> keyCol) {
		if (dataList == null || dataList.size() == 0)
			return -1;
		int dbindex = dbPool.getDbIndex(dbkey);
		Connection conn = dbPool.getConn(dbindex);
		if (conn == null)
			return -2;

		List<Map<String, Object>> mapList = SqlAdapter.getSqlMapDataList(dataList);
		int ret = this.updateMapObjects(dbkey, tableName, mapList, keyCol, conn, true);
		try {
			conn.close();
		} catch (SQLException e) {
			logger.error("" + e);
		}
		return ret;
	}

	/**
	 * 
	 * @param dbkey
	 * @param tableName
	 * @param dataList
	 * @param keyCol
	 * @return
	 */
	public int updateOneObject(long dbkey, String tableName, Object obj, Set<String> keyCol) {
		if (obj == null)
			return -1;
		int dbindex = dbPool.getDbIndex(dbkey);
		Connection conn = dbPool.getConn(dbindex);
		if (conn == null)
			return -2;

		List<Object> objList = new ArrayList<Object>();
		objList.add(obj);
		List<Map<String, Object>> mapList = SqlAdapter.getSqlMapDataList(objList);
		int ret = this.updateMapObjects(dbkey, tableName, mapList, keyCol, conn, true);
		try {
			conn.close();
		} catch (SQLException e) {
			logger.error("" + e);
		}
		return ret;
	}

	/**
	 * 
	 * @param dbkey
	 * @param tableName
	 * @param dataList
	 * @param keyCol
	 * @return
	 */
	public int deleteOneObject(long dbkey, String tableName, Object obj, Set<String> keyCol) {
		if (obj == null)
			return -1;
		int dbindex = dbPool.getDbIndex(dbkey);
		Connection conn = dbPool.getConn(dbindex);
		if (conn == null)
			return -2;

		List<Object> objList = new ArrayList<Object>();
		objList.add(obj);
		List<Map<String, Object>> mapList = SqlAdapter.getSqlMapDataList(objList);
		int ret = this.deleteMapObjects(dbkey, tableName, mapList, keyCol, conn, true);
		try {
			conn.close();
		} catch (SQLException e) {
			logger.error("" + e);
		}
		return ret;
	}

	/**
	 * 修改数据
	 * 
	 * @param dbkey
	 *            按照此key作为分库的依据
	 * @param sql
	 *            prepare sql
	 * @param parameters
	 *            占位符参数
	 * @return int
	 */
	@SuppressWarnings("rawtypes")
	public int update(long dbkey, String sql, ArrayList parameters) {
		return executeUpdate(dbkey, sql, parameters);
	}

	/**
	 * 插入数据
	 * 
	 * @param dbkey
	 *            按照此key作为分库的依据
	 * @param sql
	 *            prepare sql
	 * @param parameters
	 *            占位符参数
	 * @return int
	 */
	@SuppressWarnings("rawtypes")
	public int insert(long dbkey, String sql, ArrayList parameters) {
		return executeUpdate(dbkey, sql, parameters);
	}

	@SuppressWarnings({ "unchecked" })
	protected int insertMapObjects(long dbkey, String tableName, List<?> dataList, Connection conn) {

		if (dataList == null || dataList.size() == 0)
			return -1;

		Map<String, Object> typeVal = (Map<String, Object>) dataList.get(0);
		String sql = "";
		PreparedStatement smt = null;
		boolean bgen = true;
		int i = 0;
		try {

			conn.setAutoCommit(false);

			// insert batch values
			for (i = 0; i < dataList.size(); i++) {

				// update 的时候会删除 smt, 从新生成
				if (bgen) {
					sql = SqlAdapter.getSqlInsertStr(tableName, (Map<String, Object>) dataList.get(i));
					smt = conn.prepareStatement(sql);
					bgen = false;
				}

				int cur = 1;
				typeVal = (Map<String, Object>) dataList.get(i);
				for (Map.Entry<String, Object> en : typeVal.entrySet()) {
					smt.setObject(cur++, en.getValue());
				}
				smt.execute();
			}
			conn.commit();

		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logger.error("" + e);
			}
			logger.info("do sql:{}", sql);
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage());
		} finally {
			dbPool.closeConnection(null, smt, null);
		}
		return 1;
	}

	/**
	 * 
	 * @param dbkey
	 * @param tableName
	 * @param dataList
	 * @param keyCol
	 * @return
	 */
	public int addObjects(long dbkey, String tableName, List<?> dataList) {

		if (dataList == null || dataList.size() == 0)
			return -1;

		Connection conn = null;
		PreparedStatement smt = null;
		ResultSet rs = null;
		try {
			int dbindex = dbPool.getDbIndex(dbkey);

			conn = dbPool.getConn(dbindex);
			if (conn == null )
				return -2;
			
			conn.setAutoCommit(false);
			List<Map<String, Object>> mapList = SqlAdapter.getSqlMapDataList(dataList);
			this.addObjectsMapByConn(tableName, mapList, conn, "");
			conn.commit();

		} catch (Exception e) {
			logger.info("" + e);
			try {
				if (conn != null )
					conn.rollback();
			} catch (SQLException e1) {
				logger.info("" + e);
			}
		} finally {
			dbPool.closeConnection(rs, smt, conn);
		}
		return 1;
	}

	/**
	 * 
	 * @param dbkey
	 * @param tableName
	 * @param dataList
	 * @param keyCol
	 * @return
	 */
	public int addObjectsMap(long dbkey, String tableName, List<Map<String, Object>> mapList) {

		if (mapList == null || mapList.size() == 0)
			return -1;

		Connection conn = null;
		PreparedStatement smt = null;
		ResultSet rs = null;
		try {
			int dbindex = dbPool.getDbIndex(dbkey);

			conn = dbPool.getConn(dbindex);
			if ( conn == null )
				return -2;
			conn.setAutoCommit(false);
			this.addObjectsMapByConn(tableName, mapList, conn, "");
			conn.commit();

		} catch (Exception e) {
			logger.info("" + e);
			try {
				if ( conn != null )
					conn.rollback();
			} catch (SQLException e1) {
				logger.info("" + e);
			}
		} finally {
			dbPool.closeConnection(rs, smt, conn);
		}
		return 1;
	}

	public int addObjectsMapByConn(String tableName, List<Map<String, Object>> dataList, Connection conn, String prefix) {
		if ( ImpCollUtil.isNullColl(dataList) ){
			return 1;
		}
		
		Map<String, Object> typeVal = (Map<String, Object>) dataList.get(0);
		PreparedStatement smt = null;
		int i = 0;

		try {
			// insert batch values
			for (i = 0; i < dataList.size(); i++) {

				String sql = SqlAdapter.getSqlInsertStr(tableName, (Map<String, Object>) dataList.get(i));
				sql = prefix + sql;
				smt = conn.prepareStatement(sql);

				int cur = 1;
				typeVal = (Map<String, Object>) dataList.get(i);
				for (Map.Entry<String, Object> en : typeVal.entrySet()) {
					Object dataObject = en.getValue();
					smt.setObject(cur++, SqlAdapter.getSqlFieldValueObject( tableName, en.getKey(), dataObject, null));
				}
				smt.execute();
			}
			
			boolean bOut = false;
			if ( bOut ){
				logger.error("genSql: {}\r\n, errData:{}\r\n",  SqlAdapter.getSqlInsertStrWithValue(tableName, dataList.get(i)), dataList.get(i));
			}

		} catch (Exception e) {
			logger.error("genSql: {}\r\n, errData:{}\r\n",  SqlAdapter.getSqlInsertStrWithValue(tableName, dataList.get(i)), dataList.get(i));
			logger.error("", e);
			throw new RuntimeException(e.getMessage());
		} finally {
			dbPool.closeConnection(null, smt, null);
		}
		return 1;
	}

	/**
	 * 
	 * @param dbkey
	 * @param tableName
	 * @param dataList
	 * @param keyCol
	 * @return
	 */
	public int addOneObject(long dbkey, String tableName, Object obj) {

		if (obj == null)
			return -1;

		List<Object> objList = new ArrayList<Object>();
		objList.add(obj);
		Connection conn = null;
		PreparedStatement smt = null;
		ResultSet rs = null;
		try {
			int dbindex = dbPool.getDbIndex(dbkey);

			conn = dbPool.getConn(dbindex);
			List<Map<String, Object>> mapList = SqlAdapter.getSqlMapDataList(objList);
			this.insertMapObjects(dbkey, tableName, mapList, conn);

		} finally {
			dbPool.closeConnection(rs, smt, conn);
		}
		return 1;
	}

	/**
	 * 
	 * @param dbkey
	 * @param tableName
	 * @param dataList
	 * @param keyCol
	 * @return
	 */
	public int addOrUpdateAnyOne(long dbkey, String tableName, Object obj, Set<String> keyCol) {

		Connection conn = null;
		PreparedStatement smt = null;
		ResultSet rs = null;
		try {
			int dbindex = dbPool.getDbIndex(dbkey);

			conn = dbPool.getConn(dbindex);
			Map<String, Object> mapData = SqlAdapter.getSqlMapData(obj);
			this.addOrUpdateAnyOneMap(dbkey, tableName, mapData, keyCol, conn);

		} finally {
			dbPool.closeConnection(rs, smt, conn);
		}
		return 1;
	}

	protected int addOrUpdateAnyOneMap(long dbkey, String tableName, Map<String, Object> obj, Set<String> keyCol, Connection outConn) {

		if (obj == null || obj.size() == 0)
			return -1;

		Connection conn = outConn;
		PreparedStatement smt = null;
		try {
			if (conn == null) {
				int dbindex = dbPool.getDbIndex(dbkey);
				conn = dbPool.getConn(dbindex);
			}

			String sql = SqlAdapter.getSqlInsertStr(tableName, obj);
			try {
				smt = conn.prepareStatement(sql);
				int cur = 1;
				for (Map.Entry<String, Object> en : obj.entrySet()) {
					smt.setObject(cur++, en.getValue());
				}

			} catch (SQLException e1) {
				logger.error(e1.getMessage(), e1);
				return -3;
			}

			try {
				smt.execute();
			} catch (SQLException e) {
				logger.info("insert failed, do update->" + e);
				if (e.getSQLState().equals("23505") && keyCol != null) {

					List<Map<String, Object>> listData = new ArrayList<Map<String, Object>>();
					listData.add(obj);

					if (updateMapObjects(dbkey, tableName, listData, keyCol, conn, false) < 0) {
						logger.error(" try update failed, e:" + e);
						return -2;
					}

				} else {
					logger.error(e.getMessage(), e);
					throw new RuntimeException(e.getMessage());
				}
			}
		} finally {
			dbPool.closeConnection(null, smt, outConn == null ? null : conn);
		}
		return 1;
	}

	/**
	 * 执行操作数据
	 * 
	 * @param dbkey
	 *            按照此key作为分库的依据
	 * @param sql
	 *            prepare sql
	 * @param parameters
	 *            占位符参数
	 * @return int
	 */
	@SuppressWarnings("rawtypes")
	public int executeUpdate(long dbkey, String sql, ArrayList parameters) {
		logger.info("start executeUpdate");
		Connection conn = null;
		PreparedStatement smt = null;
		ResultSet rs = null;
		int num = 0;
		try {
			int dbindex = dbPool.getDbIndex(dbkey);
			logger.info("execute sql={},params={},dbkey={}", sql, (null == parameters ? "" : parameters.toString()), dbkey);

			conn = dbPool.getConn(dbindex);
			smt = conn.prepareStatement(sql);
			if (parameters != null) {
				for (int i = 0; i < parameters.size(); i++) {
					smt.setObject(i + 1, parameters.get(i));
				}
			}
			num = smt.executeUpdate();
			logger.info("execute result={}", num);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage());
		} finally {
			dbPool.closeConnection(rs, smt, conn);
			logger.info("end executeUpdate");
		}
		return num;
	}

	/**
	 * 执行批量操作数据
	 * 
	 * @param dbkey
	 *            按照此key作为分库的依据
	 * @param sql
	 *            prepare sql
	 * @param parameters
	 *            占位符参数
	 * @return int
	 */
	@SuppressWarnings("rawtypes")
	public int[] executeBatchUpdate(long dbkey, String sql, ArrayList parameterList) {
		logger.info("start executeUpdate");
		Connection conn = null;
		PreparedStatement smt = null;
		ResultSet rs = null;
		try {
			int dbindex = dbPool.getDbIndex(dbkey);
			logger.info("execute sql={},params={},dbkey={}", sql, (null == parameterList ? "" : parameterList.toString()), dbkey);

			conn = dbPool.getConn(dbindex);
			smt = conn.prepareStatement(sql);
			if (parameterList != null) {
				for (int i = 0; i < parameterList.size(); i++) {
					@SuppressWarnings("unchecked")
					ArrayList<Object> parameters = (ArrayList<Object>) parameterList.get(i);
					for (int j = 0; j < parameters.size(); j++) {
						smt.setObject(j + 1, parameters.get(j));
					}
					smt.addBatch();
				}
			}
			int[] num = smt.executeBatch();

			logger.info("execute result={}", num);
			return num;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			logger.error("nextException", e.getNextException());
			throw new RuntimeException(e.getMessage());
		} finally {
			dbPool.closeConnection(rs, smt, conn);
			logger.info("end executeUpdate");
		}
	}

	/**
	 * 查询数据 返回一条或多条结果集
	 * 
	 * @param dbkey
	 *            按照此key作为分库的依据
	 * @param sql
	 *            prepare sql
	 * @param parameters
	 *            占位符参数
	 * @param callBack
	 *            回调函数
	 * @return Object
	 */
	@SuppressWarnings({ "rawtypes" })
	public Object executeQuery(long dbkey, String sql, ArrayList parameters, CallBack callBack) {
		logger.info("start executeQuery");
		Connection conn = null;
		PreparedStatement smt = null;
		ResultSet rs = null;
		try {
			int dbindex = dbPool.getDbIndex(dbkey);
			logger.info("execute sql={},params={},dbkey={}", sql, (null == parameters ? "" : parameters.toString()), dbkey);

			conn = dbPool.getConn(dbindex);
			smt = conn.prepareStatement(sql);
			if (parameters != null) {
				for (int i = 0; i < parameters.size(); i++) {
					smt.setObject(i + 1, parameters.get(i));
				}
			}
			rs = smt.executeQuery();
			logger.info("execute success");
			return callBack.getResultObject(rs);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage());
		} finally {
			dbPool.closeConnection(rs, smt, conn);
			logger.info("end executeQuery");
		}
	}

	/**
	 * 执行DDL操作数据,创建表，修改表结构
	 * 
	 * @param dbkey
	 *            按照此key作为分库的依据
	 * @param sql
	 *            prepare sql
	 * @return boolean
	 */
	public boolean executeDDLSql(long dbkey, String sql) {
		logger.info("start executeDDLSql");
		Connection conn = null;
		PreparedStatement smt = null;
		ResultSet rs = null;

		boolean isSuccess = false;
		try {
			int dbindex = dbPool.getDbIndex(dbkey);
			logger.info("execute executeDDLSql sql={},dbkey={}", sql, dbkey);

			conn = dbPool.getConn(dbindex);
			smt = conn.prepareStatement(sql);

			int num = smt.executeUpdate();
			if (num == 0) {
				isSuccess = true;
			}
			logger.info("execute executeDDLSql result={}", isSuccess);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage());
		} finally {
			dbPool.closeConnection(rs, smt, conn);
			logger.info("end executeDDLSql execute");
		}
		return isSuccess;
	}

	/**
	 * 查询数据量
	 * 
	 * @param dbkey
	 *            按照此key作为分库的依据
	 * @param sql
	 *            prepare sql
	 * @param parameters
	 *            占位符参数
	 * @param callBack
	 *            回调函数
	 * @return int
	 */
	@SuppressWarnings({ "rawtypes" })
	public int executeCount(long dbkey, String sql, ArrayList parameters, CallBack callBack) {
		logger.info("start executeCount");
		Connection conn = null;
		PreparedStatement smt = null;
		ResultSet rs = null;
		try {
			int dbindex = dbPool.getDbIndex(dbkey);
			logger.info("execute sql={},params={},dbkey={}", sql, (null == parameters ? "" : parameters.toString()), dbkey);

			conn = dbPool.getConn(dbindex);
			smt = conn.prepareStatement(sql);
			if (parameters != null) {
				for (int i = 0; i < parameters.size(); i++) {
					smt.setObject(i + 1, parameters.get(i));
				}
			}
			logger.info("execute success");
			return smt.getFetchSize();
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage());
		} finally {
			dbPool.closeConnection(rs, smt, conn);
			logger.info("end executeQuery");
		}
	}

	public List<Map<String, Object>> doSelect(long dbKey, String tableName, Map<String, Object> objMap) {

		if (objMap == null || objMap.size() == 0)
			return null;

		String sql = "";
		Statement smt = null;
		int dbindex = dbPool.getDbIndex(dbKey);
		Connection conn = dbPool.getConn(dbindex);
		ResultSet rs = null;
		try {

			sql = SqlAdapter.getSqlSelectStr(tableName, objMap);
			smt = conn.createStatement();
			rs = smt.executeQuery(sql);
			return PgBeanUtil.ResultSetToMapObj(rs);

		} catch (SQLException e) {
			logger.info("do sql:{}", sql);
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage());
		} finally {
			dbPool.closeConnection(null, smt, conn);
		}
	}

	public Connection doTransaction(long dbkey) {

		Connection conn = null;
		int dbindex = dbPool.getDbIndex(dbkey);
		conn = dbPool.getConn(dbindex);
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			logger.error("" + e);
		}
		return conn;
	}

	public Connection doTransactionEnd(Connection conn, boolean bok) {

		try {
			if (bok)
				conn.commit();
			else
				conn.rollback();
			dbPool.closeConnection(null, null, conn);
		} catch (SQLException e) {
			logger.error("" + e);
		}
		return conn;
	}
	
	public Map<String, Object> doSqlOfSimple(long dbkey, String sql, boolean Query) {
		List<Map<String, Object>> aa = doSql(dbkey, sql, Query);
		if ( ImpCollUtil.isNullColl(aa) )
			return null;
		else 
			return aa.get(0);
	}

	public List<Map<String, Object>> doSql(long dbkey, String sql, boolean query) {

		Connection conn = null;
		Statement smt = null;
		ResultSet rs = null;
		try {
			int dbindex = dbPool.getDbIndex(dbkey);
			conn = dbPool.getConn(dbindex);
			smt = conn.createStatement();
			if (!query) {
				Boolean b = smt.execute(sql);

				List<Map<String, Object>> retList = new ArrayList<Map<String, Object>>();
				Map<String, Object> smapMap = new HashMap<String, Object>();
				smapMap.put("ret", b);
				retList.add(smapMap);
				logger.info(" run sql sucess, sql:{}r\n query:{}, size:{}", sql, query, retList.size());
				return retList;
			} else {
				rs = smt.executeQuery(sql);
				logger.info(" run sql sucess, sql:{}r\n query:{}, rs:{}", sql, query, rs);
				return PgBeanUtil.ResultSetToMapObj(rs);
			}
		} catch (SQLException e) {
			logger.info(" run sql failed, sql:{}\r\n query:{}, rs:{}", sql, query, rs);
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage());
		} finally {
			dbPool.closeConnection(rs, smt, conn);
		}
	}
}
